﻿using NPOI.HSSF.Model;
using NPOI.HSSF.Record;
using NPOI.HSSF.Record.Aggregates;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.Formula;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Star.Tools.Excel.Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Star.Tools.Excel
{
    /// <summary>
    ///  使用Npoi导入Excel
    /// </summary>
   public class ImportNpoiHelper
    {
        /// <summary>
        /// 导入
        /// </summary>
        public ImportNpoiHelper()
        {
        }

        #region
        
        Workbook CreateWorkbook(IWorkbook iWorkbook)
        {
            var worksheets = new List<Worksheet>();
            var workbook = new Workbook(worksheets);
            AddToNames(workbook.Names, iWorkbook);
            string[] names = workbook.Names.Select(name => name.Name).ToArray();
            for (int i = 0; i < iWorkbook.NumberOfSheets; i++)
            {
                ISheet sheet = iWorkbook.GetSheetAt(i);
                Worksheet worksheet = CreateWorksheet(sheet, i);
                worksheet.IsHidden = iWorkbook.IsSheetHidden(i);
                AddToValidations(worksheet.Validations, sheet, names);
                worksheets.Add(worksheet);
            }
            return workbook;
        }
        static void AddToNames(ICollection<NamedRange> names, IWorkbook workbook)
        {
            string propName;
            ExcelFormat version;
            if (workbook as HSSFWorkbook != null)
            {
                propName = "names";
                version = ExcelFormat.xls;
            }
            else
            {
                if (workbook as XSSFWorkbook != null)
                {
                    propName = "namedRanges";
                    version = ExcelFormat.xlsx;
                }
                else return;
            }

            var namedRanges = ((IList)workbook.GetType()
                .GetField(propName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly)
                .GetValue(workbook));

            foreach (IName name in namedRanges)
            {
                if (name.RefersToFormula.Contains("://")) continue;

//                Range range =
//NpoiHelper.ParseRange(name.RefersToFormula, version);
//                if (range == null) continue;

//                names.Add(new NamedRange
//                {
//                    Name = name.NameName,
//                    Range = range
//                });
            }
        }

        Worksheet CreateWorksheet(ISheet sheet, int index)
        {
            var rows = new List<IRow>();
            int maxColumns = 0;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                    maxColumns = Math.Max(maxColumns, row.LastCellNum);
                rows.Add(row);
            }
            return new Worksheet(sheet.SheetName, index, maxColumns, rows.Select(row => CreateRow(row, maxColumns)).ToArray());
        }

        static void AddToValidations(ICollection<DataValidation> validations, ISheet sheet, string[] names)
        {
            var hssfSheet = sheet as HSSFSheet;
            if (hssfSheet != null)
            {
                AddToValidations(validations, hssfSheet, names);
            }
            else
            {
                var xssfSheet = sheet as XSSFSheet;
                if (xssfSheet != null)
                {
                    AddToValidations(validations, xssfSheet, names);
                }
            }
        }

        static void AddToValidations(ICollection<DataValidation> validations, HSSFSheet sheet, string[] names)
        {
            InternalSheet internalSheet = sheet.Sheet;
            var dataValidityTable = (DataValidityTable)internalSheet.GetType()
                .GetField("_dataValidityTable", BindingFlags.NonPublic | BindingFlags.Instance)
                .GetValue(internalSheet);
            if (dataValidityTable == null) return;

            var validationList = (IList)dataValidityTable.GetType()
                .GetField("_validationList", BindingFlags.NonPublic | BindingFlags.Instance)
                .GetValue(dataValidityTable);
            foreach (DVRecord record in validationList)
            {
                var formula = (Formula)record.GetType()
                    .GetField("_formula1", BindingFlags.NonPublic | BindingFlags.Instance)
                    .GetValue(record);

                var validation = new DataValidation
                {
                   // Range = NpoiHelper.ParseRange(record.CellRangeAddress.CellRangeAddresses[0].FormatAsString(), ExcelVersion.Xls)
                };

                NPOI.SS.Formula.PTG.Ptg ptg = formula.Tokens[0];
                var namePtg = ptg as NPOI.SS.Formula.PTG.NamePtg;
                if (namePtg != null)
                {
                    validation.Type = DataValidationType.Formula;
                    validation.Name = names.ElementAt(namePtg.Index);
                }
                else
                {
                    var stringPtg = ptg as NPOI.SS.Formula.PTG.StringPtg;
                    if (stringPtg != null)
                    {
                        validation.Type = DataValidationType.List;
                        validation.List = stringPtg.Value.Split('\0');
                    }
                    else continue;
                }

                validations.Add(validation);
            }
        }

        static void AddToValidations(ICollection<DataValidation> validations, XSSFSheet sheet, string[] names)
        {
            CT_DataValidations dataValidations = ((CT_Worksheet)sheet.GetType()
                .GetField("worksheet", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly)
                .GetValue(sheet)).dataValidations;
            if (dataValidations == null) return;

            foreach (CT_DataValidation dataValidation in dataValidations.dataValidation)
            {
                if (dataValidation.formula1 == null) continue;

                //var range = NpoiHelper.ParseRange(dataValidation.sqref, ExcelVersion.Xlsx);
                //if (range == null) continue;

                //var validation = new DataValidation { Range = range };

                //if (names.Contains(dataValidation.formula1))
                //{
                //    validation.Type = DataValidationType.Formula;
                //    validation.Name = dataValidation.formula1;
                //}
                //else
                //{
                //    validation.Type = DataValidationType.List;
                //    validation.List = dataValidation.formula1.Trim('\"').Split(',');
                //}

                //validations.Add(validation);
            }
        }

        Row CreateRow(IRow row, int columns)
        {
            if (row == null) return null;

            var cells = new List<Cell>();
            ICell[] iCells = row.Cells.ToArray();
            int skipped = 0;
            for (int i = 0; i < columns; i++)
            {
                Cell cell = null;
                if (i - skipped >= iCells.Length || i != iCells[i - skipped].ColumnIndex)
                    skipped++;
                else cell = CreateCell(iCells[i - skipped]);
                cells.Add(cell);
            }
            return null;// new Row(row.RowNum, cells);
        }
        public IFormatProvider Format = System.Globalization.NumberFormatInfo.CurrentInfo;


        Cell CreateCell(ICell cell)
        {
            string value = null;
            switch (cell.CellType)
            {
                case CellType.String:
                    value = cell.StringCellValue;
                    break;
                case CellType.Numeric:
                    value = cell.NumericCellValue.ToString(Format);
                    break;
                case CellType.Boolean:
                    value = cell.BooleanCellValue.ToString();
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            value = cell.StringCellValue;
                            break;
                        case CellType.Numeric:
                            //excel trigger is probably out-of-date
                            value = (cell.CellFormula == "TODAY()" ? DateTime.Today.ToOADate() : cell.NumericCellValue).ToString(Format);
                            break;
                    }
                    break;
            }
            return null;// new Cell(cell.RowIndex, cell.ColumnIndex, value);
        }
        #endregion



    }
}
